drop sequence if exists  book_id;
drop table if exists books;

create sequence if not exists book_id;
create table if not exists books (
                    book_id integer not null,
                    "category" integer not null,
                    title varchar(40) not null,
                    author varchar(40) not null,
                    pub_date timestamp not null,
                    price numeric(12, 2) not null,
                    amount integer not null,
                    star numeric(1) default 0,
                    cover bytea,
                    primary key (book_id),
                    unique (title)
);
create or replace function save_book (
  v_book_id bigint,
  v_category integer,
  v_title varchar,
  v_author varchar,
  v_pub_date timestamp,
  v_price numeric,
  v_amount bigint,
  v_star smallint,
  v_cover bytea
) returns refcursor
as
$$
declare
  v_ret refcursor;
  v_book_id2 integer;
begin
  if v_book_id = 0 then
    v_book_id2 := nextval('book_id');
    insert into books (book_id, "category", title, author, pub_date, price, amount, star, cover)
    values(v_book_id2, v_category, v_title, v_author, v_pub_date, v_price, v_amount, v_star, v_cover);
  else
    v_book_id2 := v_book_id;
    update books set "category" = v_category, title = v_title, author = v_author, pub_date = v_pub_date, price = v_price,
                     amount = v_amount, star = v_star, cover = v_cover
    where book_id = v_book_id2;
  end if;

  open v_ret for select book_id, "category", title, author, pub_date, price, amount, star, cover
    from books
    where book_id = v_book_id2;

  return v_ret;
end;
$$
language 'plpgsql';
